CST Periodic Inventory Value – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Imported from BI Publisher Description: Periodic Inventory Value Report Application: Bills of Material Source: Periodic Inventory Value Report (XML) Short Name: CSTRPICR_XML DB package: BOM_CSTRPICR_XMLP_PKG

Report Parameters

Legal Entity, Cost Type, Period, Cost Group, Sort Option, Item From, Item To, Category Set, Category From, Category To, Category Organization, Currency, Exchange Rate

Oracle EBS Tables Used

mtl_item_categories, mtl_categories_kfv, mtl_system_items_vl, cst_pac_item_costs, cst_cost_groups, cst_pac_receiving_values_v, mtl_system_items, mtl_categories, hr_locations, po_lookup_codes, cst_pac_periods, po_vendors, q_ic_main, q_ic_rcv

Report Categories

Enginatics, R12 only

Running This SQL Without Blitz Report

Some Oracle EBS SQL reports in this library require functions from the utility package xxen_util. Install it before running the SQL directly against your Oracle EBS database.

Download & Import Options

Resource	Link
Excel Example Output	CST Periodic Inventory Value - Detail 11-Sep-2024 062243.xlsx
Blitz Report™ XML Import	CST_Periodic_Inventory_Value.xml
Full SQL on Enginatics	www.enginatics.com/reports/cst-periodic-inventory-value/

Executive Summary

The CST Periodic Inventory Value report is specifically designed for organizations using Periodic Average Costing (PAC). In PAC, costs are calculated for a specific period (e.g., a month) rather than perpetually. This report provides the inventory valuation based on these periodic costs. It is the PAC equivalent of the standard “Inventory Value Report”.

Business Challenge

PAC Valuation: Standard inventory reports often use the Perpetual cost (Frozen/Average). PAC users need reports that reflect the specific periodic cost calculated by the PAC engine.
Acquisition Costs: PAC often includes complex acquisition cost adders (freight, duty) that need to be visualized.
Period-Specific: The value of an item changes from period to period in PAC; this report captures the value for a specific PAC period.

Solution

This report lists inventory quantities and values based on the Periodic Average Cost.

Key Features:

PAC Period: Parameters allow selection of the specific Costing Period.
Cost Group: PAC is often driven by Cost Groups; this report supports that structure.
Receiving Value: Can also report on the value of goods in Receiving (using CST_PAC_RECEIVING_VALUES_V).

Architecture

The report queries the PAC-specific tables, which are distinct from the perpetual costing tables.

Key Tables:

CST_PAC_ITEM_COSTS: Stores the calculated periodic item costs.
CST_PAC_PERIODS: Defines the PAC periods.
CST_COST_GROUPS: Cost groups used in PAC.
CST_PAC_RECEIVING_VALUES_V: View for receiving value in PAC.

Impact

Regulatory Compliance: Essential for companies in jurisdictions that require Periodic Average Costing (e.g., parts of Latin America).
True Costing: Provides a valuation that smooths out short-term fluctuations and incorporates full acquisition costs.
Financial Accuracy: Ensures the inventory value reported matches the specific methodology (PAC) used for the ledger.

CST Periodic Inventory Value | Oracle EBS SQL Report

Imported from BI Publisher Description: Periodic Inventory Value Report Application: Bills of Material Source: Periodic Inventory Value Report (XML) Short…